package com.season.seasonStudy.javase.j_008_database;

public class Sql {
/**
 * 
 *	dbs
 *	dbms
 *		客户端 服务器端（通过客户端连接服务器端）
 *		存数据，取数据，安全，共享
 *
 *	SQL结构化的查询语言
 * 	准备工作(DDL、DCL)
 * 	@1....DDL-数据定义语言
 * 	create（创建）对象 +名字
 * 	alter（添加）
 * 	drop（删结构 ）
 * 
 * 
 * 
 * 
 * 1.创建表空间tableapace（在c盘的database下就会看到pet.dbf）
 * 		create tablespace pet
 * 		datafile 'c:/database/pet.dbf'---datafile数据文件
 * 		size 10M;---空间大小
--2.创建用户dw（identified 识别、确认;default默认）
  create user dw identified by 123
  default tablespace pet;--(此时还不能登录用户-dw,会显示privilege-无权限)
--------------------------------------------------------------------------------------------------------------
-------------@2.....DCL-数据控制语言 grant revoke
--3.（DCL语言）给用户分配权限connect（访客）,resource（可靠角色）,sysdba（grant授予 ）
  grant connect,resource  to  dw;--分配完权限后就可以登录dw了，1、2、3、都要在system下进行
--------------------------------------具体用户--------------------------------------------------------------------------
  表：存放数据的基本单位  
  行：一个实体的数据
  列：组成表的结构
----------------------------数据类型:-----------------------------------------------------------
           数字：number（数位，小数位），
           文字：char、varchar2(长度)、nchar、nvarchar2 ，
           日期：date、timestamp
 --#1.创建student表，（）中写的是表student的结构
   create table student (
             id number(4),
             name varchar2(20), --not null,--作为主键必填not null
             sex char(2),
             born date,
             acount number(8,2)--共8位数，有两位小数
     )---表里出现的ROWID删除了就不能编辑表格了
   --往student表中添加address列
    alter table student
         add address varchar2(100);
   --#1.1创建class表
   create table class (
       classid number(4),--年级号
        name varchar2(20)--年级名称
    )
   alter table student     
         add classid number  --添加其他表中的列 
  -------在system下将权限scott.emp分配给用户dw
   grant select on scott.emp to dw;
   -------就可以在用户dw下查询Scott的表emp
   select *from scott.emp;
   -------如何把别人的表导入当前用户
   create table emp as select * from scott.emp
   ---把别人的表导入自己内已经存在的表（外键关系不会导进来）   
   insert into student(id,name,born)
          select empno,ename,hiredate from scott.emp   
          
 -----把scott中的emp 和dept导入system中    
 select *from dept
 create table emp (
    EMPNO number(4),
    ENAME varchar2(20),
    JOB varchar(20),
    MGR number(4),
    HIREDATE date,
    SAL number(8,2),
    COMM number(8,2),
    DEPTNO number(4)
 )
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
   select empno,ename,job,mgr,hiredate,sal,comm,deptno from scott.emp
 
 
 create table dept (
    DEPTNO number(4),
    DNAME varchar2(20),
    LOC varchar2(20)
 )
 insert into dept(deptno,dname,loc)select deptno,dname,loc from scott.dept
----------------------------------------------------------------------------------------------------------------------------
-------------添加约束
  ---域完整性约束 --使用check约束     constraint（约束）   
  alter table student --约束名  sex这一列只能写男或女
        add constraint ck_student_sex check (sex='男' or sex='女')
  alter table student --约束名  born这一列只能在这个范围里写
        add constraint ck_student_born 
              check (born>=to_date('2010-1-1','YYYY-mm-dd')
              and born<=to_date('2015-1-1','YYYY-mm-dd'))
  ---实体完整性约束
      ---#2.主键约束（保证数据唯一可靠不重复）
格式：alter table 表名 add constraint(约束) pk_表名_主键列 primary key (主键列);
  alter table student --比较规范的约束名(pk_student_id )  
        add constraint pk_student_id primary key(id)---在student表中设id作为主键
  alter table class 
        add constraint pk_class_id primary key(classid)---在class表中设classid作为主键

alter table emp --比较规范的约束名(pk_emp_empno )  
        add constraint pk_emp_empno primary key(empno)---在emp表中设empno作为主键
  alter table dept 
        add constraint pk_dept_deptno primary key(deptno)---在dept表中设deptno作为主键
alter table emp
         add constraint fk_emp_dept foreign key(deptno)---在外键表dept中必须有deptno 
                 references dept(deptno)---在主键表emp中主键必须是empno        
        
        
        
        
  ---引用完整性约束
      ---外键约束
格式：alter table 外键表 add constraint  fk_外键表_主键表  foreign (外来的) key(外键列)
                references(引用) 主键表(主键列)     
  alter table student
         add constraint fk_student_class foreign key(classid)---在外键表student中必须有classid  
                 references class(classid)---在主键表class中主键必须是classid
------------------------------删除
--------删除表空间（只能删除自己创建的表空间，其他的不要动，否则数据库会出错）
     drop tablespace pet including contents and  datafiles---contents内容
--------删除创建的用户
        ----如果要删除的用户已经创建了表,那么就需要在删除时带一个参数cascade,就会把用户及该用户的表删除
     drop user dw cascade
--------回收权限（revoke取消）(回收权限要在system下进行
     revoke connect,resource from  dw;---dw用户名
--------删除表（;代表结束）  
     drop table student;
--------删除列，删除时必须有column （定义时列的格式是什么样删除时格式就什么样）
     alter table student drop column address varchar2(100);
--------删除约束
        -----alter table 表名 drop constraint ck_表名_列名
     alter table student  drop constraint ck_student_born 
--------删除主外键关联
    -- alter table 外键表 drop constraint fk_外键表_主键表
   alter table score drop constraint fk_score_student
   ---删除scoreid，student
-------------------------------------------------------------------------------------------------------------
------------添加序列
   create sequence seq_student
   start with 1---从1开始
   increment by 1--一次增长1
   cache 10;--缓存到10
   create sequence seq_emp
   start with 1
   increment by 1
   cache 10;
   create sequence seq_dept
   start with 1
   increment by 1
   cache 10;
---------------------------------重点工作DML---------------------------------------------------------------
----DML-数据处理语言（insert（增加数据）、update(更新数据)、delete（删数据））
    ----------insert into 表名 (列名，列名,................)
    ------给class表添加信息
   --seq_student.nextval产生随机数,values对原有的列进行赋值
   --必须创建了序列seq_student才可以有nextval
     不写commit的话就点箭头，否则提交不了数据没法运行，写了就直接点击齿轮就可以commit语句可以提交事
务，当执行了commit语句后，会确认事务的变化、结束事务、删除保存点、释放锁,当使用commit语句结束事务
后，其他会话可以查看到事物变化后的新数据;如果不commit，那么数据只会累积
   insert into class (classid,name) values (seq_student.nextval,'admin');
        commit;
   insert into class (classid,name) values (seq_student.nextval,'bfg');
   ------------update 表 set 列名1='’值',列名2='’值2'   where 条件     
   update class set name='SMITH'where classid=32 or classid=33 --当id=5或者id=6时，才对这一行中的name赋值为'SMITH'
    -----update 希望emp表中的SCOTT的岗位、工资、补助与SMITH一样
  update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH')where ename='SCOTT'
    -------------delete from 表名 where 条件
   delete from class where classid=32 or classid=40--当id=32或者id=40时，就删除这一行
    -----给student表添加信息
    ----to_date('2012-2-3','YYYY-mm-dd')表示日期可以以任何格式添加
   insert into student (id,name,sex,born,acount,classid)--class表中必须要有classid=3的信息
      values (seq_student.nextval,'张三','男',to_date('2012-2-3','YYYY-mm-dd'),1234.3,3)
----------------------------------------------------------------------------------------------------------------
----------------DQL 数据查询语言,select 查询表中数据
  select classid as f ,name from class--as可以省略,f 是classid的别名不能用数字
  select * from dept
  delete dept where deptno=90
  insert into dept (deptno,dname,loc) values (50,'QAZXS','CLOOR');
  insert into dept (deptno,dname,loc) values (70,'MJU','MOTH');
  1....<>--不等于,empno<>7839
  select empno,job from emp where empno<>7839
  2....or--或者(大于或小于),empno>7839 or empno <7839,除了7839其余的都显示
select empno,job from emp where empno>7839 or empno<7839
  3....and--并且,empno>7369 and empno<7839
  select empno,job from emp where empno>7369 and empno<7839
  4....not（id=3）--非
  not(empno>7839)---不大于
  select empno,job from emp where not(empno>7839)--<=7839的都显示出来
  select empno,job from emp where not(empno=7839)
  5....between  3 and 7--范围[3,7]，等于3,4,5,6,7,的列出来
  select empno,job from emp where empno between 7698 and 7839  
  6.....id in(3,5,7)--等值匹配，等于3,5,7的列出来
  select empno,hiredate from emp where hiredate in(to_date('1981/06/09','yyyy-mm-dd'),to_date('1987/4/19','yyyy-mm-dd'))
  ------------模糊查询（只适合字符类型）like：
  7.......----找出ename为SMITH的信息
   select empno,ename from emp where ename like 'SMITH'
  8......like 'A%'---以A开头
   select empno,ename from emp where ename like 'A%'
  9.....like '%S' --以S结尾
   select empno,ename from emp where ename like '%S'
 10.....like '%A%'--只要带A就可以
   select empno,ename from emp where ename like '%A%'
 11.....like ‘_A%’--至少两个字符,第二个字符是A
    select empno,ename from emp where ename like '_A%'
 12.....like 'A_A_%'-- 至少4个字符,A在第一个和第三个，A在名字内部'_A_')
     select empno,ename from emp where ename like 'A_A_%'
 13.....like ‘_A___’--必须有五个字符(几个下划线加上A的个数就有几个字符),第二个字符是A
   select empno,ename from emp where ename like '_A___'
 14.....like 'A____'--必须有五个字符,首字符为A
 -------------------------排序
              升序 order by empno asc（系统默认升序）
              降序 order by empno desc，
        account is null--为空
        account is not null==not(account is null)--不是空
---先主(job)后次(sal ):where job is not null order by job desc,sal 
---管辖范围(就近原则)：where job is not null order by sal ,job desc(desc只对job降序,而sal 升序））
-----------job is not null 作用是：不把 job为空的计算在内
    select * from emp where sal is not null order by sal asc--即使不写asc也会对sal升序排序
    select * from emp where job is not null order by job asc,sal--先对首次出现的列job进行升序,在job相等时再对sal升序
    select * from emp where sal is not null order by sal desc--对sal降序
    select * from emp where job is not null order by job desc,sal--先对job降序,在job相等时再对sal 升序
    select * from emp where job is not null order by job,sal desc--先对job升序,在job相等时再对sal 降序
--------------------------------------------------------------
  ---------一般函数
  select to_char(hiredate,'YYYY'),name,nvl(comm,0) from emp--nvl(comm,0)如果每个单元格为空转换为0输出
  ---------聚合函数count ,sum,avg,min,max  
  select count (comm),sum(comm),avg(comm),max(comm),min(comm) from emp
  --把空计算在外count(comm)、avg(comm)把空排除在外
   select count (*),sum(comm),avg(comm),max(comm),min(comm) from emp
    select count (*),sum(nvl(comm,0)),avg(nvl(comm,0)),max(comm),min(comm) from emp
   --把空排除出在内count(*)把空计算在内avg(comm)把空排除在外
   select count (*),sum(comm),avg(nvl(comm,0)),max(comm),min(comm) from emp
    ---avg(nvl(comm,0))把空补为0，再计算平均数
-----------一般函数不能和聚合函数在一块运算，必须把一般函数放在group by中，否则会出错
    select to_char(hiredate,'yyyy'),count(*),sum(comm),avg(nvl(comm,0)) from emp 
         group by to_char(hiredate,'yyyy')--先过滤后聚合,过滤条件是to_char(hiredate,'yyyy')
         having count(*)>1 --聚合函数的条件写在having中
         order by count(*) desc--降序,必须是前边有的才可以排序
      这一行代码的意思是：根据年份过滤,把相同年份的放在一个单元格,count(*)计算某一个相同年份的个
数,sum(comm),avg(nvl(comm,0))计算某一个相同年份下comm的和及平均数,显示count(*)>1的,对count(*)>1进行降序
      select deptno,count(*) from emp group by deptno
  
   -------------------------------------------------------------
   做业：
   1.添加课程表（编号（主见）），名字，课时（>0）,简介
   2.添加成绩表（编号（主见）），课程号，学号，分数（0~100），考试时间
 --------------------------------------------------------------
 ----保存点，恢复编辑数据之前的一种方法，别的方法呢？？？？？？？
 insert into emp(empno,ename,mgr,sal)values(9999,'QWER',7369,1000);
 insert into emp(empno,ename,mgr,sal)values(1111,'ASDD',2222,2000);
 savepoint a1;---创建保存点，从创建保存点到会回滚之间不能点绿色箭头
 delete from emp where empno=1111;
 savepoint a2;---创建保存点
 delete from emp where empno=9999;
 rollback to a2;---回滚到想要的点
 rollback;----回滚到最初
----------------------------------------------------------------------------------  
 ------------------------数据合并，行合并------------------------------------------------------------------  
 ---并集union 合并，除去重复的，union all 合并，不除去重复的
 ---减集minus 以(7369,7499,7521)为准，去除(7566,7521)和前边重复的内容
 ---交集intersect 
  select *from emp where empno in(7369,7499,7521)  
 -- union 
 -- select *from emp where empno in(7566,7521)  
  intersect 
  select *from emp where empno in(7566,7521)   
 ----------------------------数据合并，列合并-----------------------------------------------------------
 ------------------1...表关联 ,from后边跟两个表用',' 隔开,条件用where
   select * from dept,emp--表emp跟在dept后边,dept.deptno与emp.deptno不对应，所有的都显示,每一条dept信息对应所有的emp信息
   select * from dept,emp where dept.deptno=emp.deptno--dept.deptno与emp.deptno对应,只显示两个deptno相等的行
   格式：select * from 表1 a，表2 b where a.主键=b.外键
-----查询各个部门的个数
select dept.deptno,dept.dname,count(*) from emp,dept 
 where emp.deptno=dept.deptno
  group by dept.deptno,dept.dname
-----查询部门名为'SALES'的部门号及个数
select dept.dname,dept.deptno,count(*) from dept,emp
 where emp.deptno=dept.deptno and  dept.dname='SALES'
    group by dept.deptno,dept.dname
 -------------------2...表联接 
 -------------2.1.....内连接inner join 条件用on
       -----(dept.deptno与emp.deptno对应,只显示两个deptno相等的行，与表关联一样,但是必须加条件on)
 格式：select * from 表1 inner join 表2 on 表1.主键=表2.外键 where 条件
                               (等同于select * from 表1,表2 where a.主键=b.外键 where 条件)
      select * from dept inner join emp on  emp.deptno=dept.deptno where dname='SALES'
      select * from dept left join emp on  emp.deptno=dept.deptno ----左链接
      select * from dept right join emp on  emp.deptno=dept.deptno----右链接
--------------2.2....外连接outer join    条件用on
--------------2.3.....左连接left outer join
 格式：select * from 表1 left outer join 表2 on 表1.主键=.表2外键
            ---（以左边表为基准；左边表有右边表没有的显示；右边有左边没有的不显示）
select * from dept left outer  join emp on emp.deptno=dept.deptno--dept.deptno=40的显示出来了
--------------2.4.....右连接right outer join
格式：select * from 表1 right outer join 表2 on 表1.主键=表2.外键
          ---（以右边表为基准：右边表有左边表没有的显示;左边有右边没有的不显示）
select * from dept right outer  join emp on emp.deptno=dept.deptno--emp.empno=1111,9999的显示出来了
---------------2.5....左右全连接full outer join
格式：select * from 表1 full outer join 表2 on 表1.主键=表2.外键
          （两个表的数据都显示出来了）
select * from dept full outer join emp on emp.deptno=dept.deptno--dept.deptno=40且emp.empno=1111,9999的都显示出来了
--------例1：找部门没有人的 （垃圾数据的找法）
格式：select * from 表1 full outer join 表2 on 表1.主键=表2.外键
            where 表1.任意列名 is null or 表2.任意列名 is null（查询结果一般数据为垃圾）            
 select * from dept full outer  join emp on emp.deptno=dept.deptno 
        where emp.empno is null or dept.deptno is null
 ---------------2.6....cross join交叉连接,跟表关联是一样的效果
 select * from dept d cross join emp
 ---------------------------------------------------------------------------------------
--------例1：查找部门人数超过三人的部门名
select dept.deptno,count(*) from emp,dept
 where emp.deptno=dept.deptno having count(*)>3
group by dept.deptno


select dept.deptno,dname,count(*) from dept,emp
 where emp.deptno=dept.deptno group by dname,dept.deptno having count(*)>5---使用关联
select dname,count(*) from emp inner join dept on emp.deptno=dept.deptno
 group by dname having count(*)>5---使用内链接

--------例2：dname=SALES部门的各岗位job人数
     select dname,job,count(*) from dept,emp where dept.deptno=emp.deptno
        group by dname,job 
            having dname='SALES'--两种方法效果是一样的,只是控制的显示方法不同
     select job,count(*) from emp inner join dept on dept.deptno=emp.deptno
         where dname='SALES'
               group by job
--------例3：查询部门平均收入超过2000的部门名称
   select dname,avg(nvl(sal,0)) from emp inner join dept on dept.deptno=emp.deptno
         having avg(nvl(sal,0))>2000---聚合函数的条件写在having中
         group by dname
--------例4：查询 hiredate 是1981年的员工名和部门名称
   select ename,dname from dept,emp where emp.deptno=dept.deptno
         and to_char(hiredate,'yyyy')='1981'
   select ename,dname from emp inner join dept on dept.deptno=emp.deptno
         where to_char(hiredate,'yyyy')='1981'
-------例5：查找经理是king的员工名和部门名(用一般方法与子查询查询显示会不同,但效果一样)
    select * from emp 
        where mgr=(select empno from emp where ename='KING' )
-------例6：查找员工smith的部门名字
    select * from emp inner join dept on emp.deptno=dept.deptno
          where ename='SMITH'  ----用内连接查询    
    select * from dept where deptno=(---当查询结果有一行时可以用 =，也可以用in
             select deptno from emp where ename='SMITH'
      )--用一般方法查询
-------例7：查找员工名字包含A的部门名字
     select * from emp inner join dept on emp.deptno=dept.deptno 
          where ename like '%A%'
     select dname from dept where deptno in(
             select deptno from emp where ename like '%A%'
      )---当查询结果有多个行时要用 in
-----------------------------3.....子查询--------------
        -----3.1.....子查询作为条件查询(只能是查一列)(不能查询个数)
----------例1：查找经理是king的员工名和部门名
    select * from emp yuangong inner join emp jingli on yuangong.mgr=jingli.empno
         where jingli.ename='KING'
---------例2：平均工资大于2000的部门名称
   select dname,avg(nvl(sal,0)) from dept inner join emp on emp.deptno=dept.deptno
        group by dname
        having  avg(nvl(sal,0))>2000---用内连接查询
---------过程：显示所有部门号（emp）->找平均工资大于2000的部门号->根据部门号找出名字（dept）
       select dname from dept where deptno in (
             select deptno from emp group by deptno having avg(sal)>2000
          )
--------例3：部门名称为SALES的平均工资
    select avg(nvl(sal,0)) from dept inner join emp on emp.deptno=dept.deptno
          where dname='SALES'
--------过程：找部门号(dept)条件是:名称为SALES->根据部门号找出工资（emp）
    select avg(sal) from emp where deptno in( 
          select deptno from dept where dname='SALES'
      )
--------例 4：查找所在部门名字是SALES且工资大于该部门平均工资的人
 ---过程：查找dname='SALES"的部门号->计算该部门下的avg(sal)->找到部门名字是SALES的sal>avg(sal)的信息
select * from emp  where deptno in(
       select deptno from dept where dname='SALES') 
             and sal>(select avg(sal) from emp where deptno in(
                   select deptno from dept where dname='SALES'
                      )
             )  
select *from emp where sal>(
       select avg(nvl(sal,0)) from emp where deptno in(
            select deptno from dept where dname='SALES'
       )
)and deptno in( 
               select deptno from dept where dname='SALES'
   )
---------例5：找1981年雇佣的工资最高的人（max(sal)）
select * from emp where to_char(hiredate,'yyyy')='1981'
and sal=(
        select max(sal) from emp 
      )
 -----找出每个部门的最高薪水和薪水总和
 select dept.dname,max(sal),sum(sal)from dept,emp where dept.deptno=emp.deptno
        group by dept.dname
---------例6：找部门是SALES的经理 
 --过程：找名字为SALES的部门号->通过部门号找到上级号->通过上级号找到上级间的关系，找出谁是经理
      select * from  emp where empno in(
           select mgr from emp where deptno in(
              select deptno from dept where dname='SALES'
              )
          )
----------例7：人数大于3的部门名
      select *from dept where deptno in(
            select deptno from emp 
               group by deptno
                   having count(*)>3
      )
----------例8：人数大于2的经理名字
      select * from emp where empno in(
           select mgr from emp 
               group by mgr
                   having count(*)>2
      )
          ---------3.2.....子查询作为列（查询结果是1行1列）
----------例1：添加了只有1000的一列
          select ename,sal,1000 from emp
---------例2：收入与平均工资的差          avgsal是前边表达式的别名
   select  ename,sal-(select avg(sal) from emp) avgsal from emp
   select sal+nvl(comm,0)-( 
         select avg(sal+nvl(comm,0)) from emp
   ) cha  from emp
---------例3：各部门人数占总人数的比重                 
  select deptno,round(count(*)/(select count(*) from emp )*100,2)||'%'--保留两位小数，显示加%
      from emp group by deptno
  select deptno,count(*)/(select count(*) from emp )--显示小数
      from emp group by deptno
  select deptno,count(*),(select count(*) from emp )
  from emp group by deptno
  select * from dept where deptno in(
    select deptno from emp 
       group by deptno
       having count(*)/(select count(*) from emp )>0.3
  )
--"||"字符串拼接,empno||ename两列放在一列中
 select empno||ename from emp
 ---------例3：查询各岗位job平均工资比总的平均工资多的岗位名称  
 select job,count(*),avg(sal+nvl(comm,0)),sum(sal+nvl(comm,0)) from emp
     group by job
        having avg(sal+nvl(comm,0))>(select avg(sal)from emp)
 ---------例4：查询Smith的收入与他所在部门的平均收入的差
 select sal,(select avg(sal) from emp 
          where deptno in(
                select deptno from emp where ename='SMITH'
           )
       group by deptno
       ) from emp where ename='SMITH'
          -------3.3.......查询结果作为表的方式(多行多列)（用于从多个表中查数据）
-----------例1：查询员工姓名，工资和所在部门的平均工资（加别名）
select *from emp inner join (
     select deptno,avg(sal) avgsal from emp
     group by deptno
) t on t.deptno=emp.deptno
------------例2：查询各个岗位最高工资的人的名字，岗位，工资
select ename,emp.job,sal from emp inner join (
      select job,max(sal) maxsal from emp group by job
)t on emp.job=t.job where sal=maxsal
------------例3：查询各年雇佣员工中工资最低的人的名字，岗位，工资
select * from emp inner join (
      select to_char(hiredate,'yyyy') as nian,min(sal) minsal from emp 
      group by to_char(hiredate,'yyyy')
)t on to_char(emp.hiredate,'yyyy')=nian where sal=minsal

-----------------例4：查询员工工资与各部门的平均工资的差
select e.*,(                     
   select avg(sal) avgsal from emp t
   where t.deptno=e.deptno--????
    group by deptno)from emp e--?????

    select ename,dname,sal,salavg,sal-salavg from emp
       inner join dept on dept.deptno=emp.deptno---？？？？？
       inner join(
            select deptno,avg(sal) salavg--(必须定别名上面才能使用)
             from emp
          group by deptno
               ) t on emp.deptno=t.deptno
       order by sal-salavg---（不能用别名）
  --查询员工收入与同年份进入公司人的平均工资的差
    select * from emp 
       inner join(
           select to_char(hiredate,'yyyy') years,avg(sal) from emp
        group by to_char(hiredate,'yyyy')
               ) t on t.years=to_char(hiredate,'yyyy')
         
---4.带关联的子查询（没有员工的部门）
  --第一种
 select dept.* from dept 
 left outer join emp on dept.deptno=emp.deptno
 where emp.empno is null
  --第二种（常用）
 select * from dept where deptno not in(
   select deptno from dept
    )
  --第三种（常用）
  select * from dept where 
     not exists (select *from emp 
             where dept.deptno=emp.deptno
            )
 
select e.* ,sal-(select avg(sal) avgsal from emp t
       where t.deptno=e.deptno
      group by deptno)from emp e
--查询员工收入与同年份进入公司人的平均工资的差
select e.*,(                     
   select avg(sal) avgsal from emp t
   where to_char(t.hiredate,'yyyy') = to_char(e.hiredate,'yyyy')
   group by to_char(t.hiredate,'yyyy')
) from emp e

---查询各部门的名称和人数(两个表取数据用关联)
select *from emp
select *from dept
  --第一种
select dname,count(empno) from dept
left outer join emp on emp.deptno=dept.deptno
group by dname
  --第二种
select dname,
nvl((select count(*) renshu from emp where emp.deptno=dept.deptno
  group by deptno),0)from dept
  --第三种
  select dname,nvl(renshu,0) from dept 
  left outer join(
  select deptno,count(empno)renshu from emp
   group by deptno
 )t on t.deptno=dept.deptno
  --第四种(性能更好一点)
  select dname,0 from dept
  where not exists(select *from emp
  where dept.deptno=emp.deptno)
  union
  select dname,count(empno) from dept,emp
  where dept.deptno=emp.deptno
  group by dname
  
  
        ----------3.5.....关键字及特殊用法
              ----@1...exists
 select *from dept where deptno in(select deptno from emp)---子查询
 select *from dept 
        where exists(
                 select * from emp where emp.deptno=dept.deptno
         )---exists
select *from dept 
        where not exists(
                 select * from emp where emp.deptno=dept.deptno
         )---not exists
------------例1：查询员工姓名，工资和所在部门的平均工资（加别名）
select ename,sal,deptno,(
     select avg(sal) from emp t1
        where t1.deptno=t2.deptno
            group by deptno
) from emp t2
------------例2：所有部门工资在3000以下的部门
select *from dept 
       where 3000>(
              select sal max(sal)from emp where emp.deptno=dept.deptno
              )
            -----@2....all （两个表关联不上的也会出来）
-----------例1：所有部门工资在3000以下的部门
select *from dept 
       where 3000>all(---数在all 的前边
              select sal from emp where emp.deptno=dept.deptno
        )---数据为空的也会显示出来
        and exists (select * from emp where emp.deptno=dept.deptno )---数据为空的不会显示出来
         -----@3....any （只要有一个就显示出来）

---输出级别
select ename, 
         ( case when sal<=1000 then '一级'   
                  when sal>1000 and sal<3000 then '二级'   
                   else '三级' end )收入级别--尽量不要用中文
from emp
---80~90之间雇佣的人数
select t1.nian,nvl(num,0) from (
       select to_char(hiredate,'yyyy') nian ,count(*) num from emp 
           group by to_char(hiredate,'yyyy')
)t1 right join(
select  '1980'nian from dual
union
select  '1981'nian from dual
union
select  '1982'nian from dual
union
select  '1983'nian from dual
union
select  '1984'nian from dual
union
select  '1985'nian from dual
union
select  '1986'nian from dual
union
select  '1987'nian from dual
union
select  '1988'nian from dual
union
select  '1989'nian from dual
)t2 on t1.nian=t2.nian



select *from (
select to_char(hiredate,'yyyy') nian ,to_char(hiredate,'mm')yue,count(*) num from emp 
           group by to_char(hiredate,'yyyy') ,to_char(hiredate,'mm')
 )t right join(                  
select *from (
select  '1981'nian from dual
union
select  '1982' from dual
union
select  '1983' from dual
)n ,(
select  '01'yue from dual
union
select  '02' from dual
union
select  '03' from dual
union
select  '04' from dual
union
select  '05' from dual
union
select  '06' from dual
)y
)t1 on t.nian=t1.nian and t.yue=t1.yue



--各部门各岗位人数
select deptno,sum(
case when job='CLERK' then renshu
  else 0 end 
)clerk,sum(
case when job='SALESMAN' then renshu
  else 0 end 
)salesman,sum(
case when job='MANAGER' then renshu
  else 0 end 
)manager,sum(
case when job='ANALYST' then renshu
  else 0 end 
)anatyst
from (
select deptno,job,count(*) renshu from emp group by deptno,job)t
group by deptno
select *from emp

 * 
 * 
 * 
 * 
 */
}
